from os.path import split

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import re


def main(metric_data_path, linyuan_template_path, url3):
    # 指标数据
    new_header0 = ['指标', '2023年', '2022年', '2021年', '2020年']

    metric_data = pd.read_excel(metric_data_path, header=None, skiprows=2)

    metric_data.columns = new_header0

    # 使用正则表达式清洗 "指标" 列
    metric_data["指标"] = metric_data["指标"].apply(
        lambda x: re.sub(r'(：)', '',
                         re.sub(r'其中：', '',
                                re.sub(r'(加：|减：)', '',
                                       re.sub(r'^\s*[一二三四五六七八九十\d]+\s*[\.、]', '',
                                              re.sub(r'\（.*?\）', '', x))))) if isinstance(x, str) else x
    )
    # 模板
    new_header1 = ['指标', '2023年', '2022年', '2021年', '2020年']
    new_header2 = ['指标', '2023年', '2022年', '2021年', '空白', '计算公式']
    new_header3 = ['指标', '2023年', '2022年', '2021年', '空白', '计算公式']
    new_header4 = ['指标', '2023年', '2022年', '2021年', '空白', '计算公式']
    new_header5 = ['指标', '2023年', '2022年', '2021年']
    new_header6 = ['指标', '2023年', '2022年', '2021年', '空白', '计算公式']

    profit_total_and_income_statement, n1 = read_and_set_header(linyuan_template_path, "利润总额与利润表", new_header1)

    net_asset_per_share_and_balance_sheet, n2 = read_and_set_header(linyuan_template_path, "每股净资产和资产负债表",
                                                                    new_header2)
    roe_and_financial_indicators, n3 = read_and_set_header(linyuan_template_path, "净资产收益率与财务指标", new_header3)

    product_gross_margin_and_income_statement, n4 = read_and_set_header(linyuan_template_path, "产品毛利率与利润表",
                                                                        new_header4)

    accounts_receivable_and_cash_flow_statement, n5 = read_and_set_header(linyuan_template_path, "应收帐款和现金流量表",
                                                                          new_header5)

    prepaid_and_current_liabilities, n6 = read_and_set_header(linyuan_template_path, "预收帐款与流动负债", new_header6)
    # 模板_工作表1
    d1(profit_total_and_income_statement, metric_data, n1)
    # 模板_工作表2
    d3(net_asset_per_share_and_balance_sheet, metric_data, n2)
    # 模板_工作表3
    d3(roe_and_financial_indicators, metric_data, n3)

    # 模板_工作表4
    d3(product_gross_margin_and_income_statement, metric_data, n4)

    # 模板_工作表5
    d3(accounts_receivable_and_cash_flow_statement, metric_data, n5)

    # 模板_工作表6
    d3(prepaid_and_current_liabilities, metric_data, n6)
    # # 更新模板文件中的数据
    update_template(linyuan_template_path, url3, profit_total_and_income_statement,
                    net_asset_per_share_and_balance_sheet,
                    roe_and_financial_indicators, product_gross_margin_and_income_statement,
                    accounts_receivable_and_cash_flow_statement, prepaid_and_current_liabilities)


def read_and_set_header(file_path, sheet_name, new_header):
    # 读取数据时跳过现有的表头行
    df = pd.read_excel(file_path, sheet_name=sheet_name, header=None, skiprows=2)
    # 读取 Excel 文件，将第一行作为列名
    # 计算现有 DataFrame 列的数量
    existing_columns_count = len(df.columns)
    # 计算需要补齐的列数
    missing_columns_count = existing_columns_count - len(new_header)
    # 如果需要补齐，添加占位符
    if missing_columns_count > 0:
        new_header.extend([f"空白{i + 1}" for i in range(missing_columns_count)])
    # 设置自定义表头
    df.columns = new_header
    df1 = pd.read_excel(file_path, sheet_name=sheet_name, header=None)  # 使用 header=None 读取原始数据
    # 获取 DataFrame 的第一行数据
    first_row = df1.iloc[0]

    num_dw = 1
    if '亿元' in first_row[0]:
        num_dw = 100000000

    return df, num_dw

def update_template(template_path, url3, *data_frames):
    # 加载模板
    wb = load_workbook(template_path)
    # 为每个数据框设置新的数据到相应的工作表
    sheet_names = wb.sheetnames
    for sheet_name, df in zip(sheet_names, data_frames):
        if sheet_name in wb.sheetnames:
            ws = wb[sheet_name]
            merged_ranges = ws.merged_cells.ranges
            # 创建一个新的数据表格
            data_sheet = pd.DataFrame(dataframe_to_rows(df, index=False, header=False))
            # 遍历所有的行和列，跳过合并单元格
            for r_idx, row in enumerate(data_sheet.itertuples(index=False), 3):
                for c_idx, value in enumerate(row, 1):
                    # 确保不写入合并单元格
                    cell = ws.cell(row=r_idx, column=c_idx)
                    if not any(cell.coordinate in merged_range for merged_range in merged_ranges):
                        cell.value = value

    # 保存更新后的文件
    wb.save(url3)


def d1(data1, data2, dw):
    # 数据处理
    write_pandas(data1, data2, dw)


def write_pandas(temp_data, source_df, dw):
    for index, row in temp_data.iterrows():
        report_date = row['指标']
        if pd.isna(report_date):
            continue

        def debug_lambda(x):
            return str(x) in report_date  # 确保 x 被转换为字符串后进行匹配

        matched_rows = source_df[source_df['指标'].apply(debug_lambda)]
        # 更新报表中对应行的“2023年”数据
        temp_data.at[index, '2023年'] = matched_rows['2023年'].values[
                                            0] / dw if not matched_rows.empty else None
        temp_data.at[index, '2022年'] = matched_rows['2022年'].values[
                                            0] / dw if not matched_rows.empty else None
        temp_data.at[index, '2021年'] = matched_rows['2021年'].values[
                                            0] / dw if not matched_rows.empty else None
        temp_data.at[index, '2020年'] = matched_rows['2020年'].values[
                                            0] / dw if not matched_rows.empty else None


def d3(data1, data2, dw):
    write_pandas3(data1, data2, dw)

def write_pandas3(temp_data, source_df, dw):
    # 遍历指标
    for index, row in temp_data.iterrows():
        name_date = row['指标']
        if pd.isna(name_date):
            continue
        dw2 = 1
        if dw == 1:
            if '亿元' in name_date:
                dw2 = 100000000

        name_date = re.sub(r'(：)', '',
                           re.sub(r'其中：', '',
                                  re.sub(r'(加：|减：)', '',
                                         re.sub(r'^\s*[一二三四五六七八九十\d]+\s*[\.、]', '',
                                                re.sub(r'\(.*?\)', '',
                                                       re.sub(r'\（.*?\）', '', name_date))))))

        def debug_lambda(x):
            return str(x) == name_date  # 确保 x 被转换为字符串后进行匹配

        matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        if "归属于母公司股东权益合计" == name_date:
            if len(matched_rows) == 0:
                name_date = "归属于母公司所有者权益合计"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        if "股东权益合计" == name_date:

            if len(matched_rows) == 0:
                name_date = "所有者权益合计"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        if "负债和股东权益总计" == name_date:
            if len(matched_rows) == 0:
                name_date = "负债和股东权益合计"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]
                if len(matched_rows) == 0:
                    name_date = "负债和所有者权益总计"
                    matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        if "归属于母公司股东的其他综合收益" == name_date:
            if len(matched_rows) == 0:
                name_date = "归属母公司所有者的其他综合收益的税后净额"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

            if len(matched_rows) == 0:
                name_date = "归属母公司股东的其他综合收益的税后净额"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        if "归属于少数股东的其他综合收益" == name_date:
            if len(matched_rows) == 0:
                name_date = "归属于少数股东的其他综合收益的税后净额"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

            if len(matched_rows) == 0:
                name_date = "归属于少数所有者的其他综合收益的税后净额"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        if "实收资本" == name_date:
            if len(matched_rows) == 0:
                name_date = "股本"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]
        if "所有者权益" == name_date:
            if len(matched_rows) == 0:
                name_date = "股东权益"
                matched_rows = source_df[source_df['指标'].apply(debug_lambda)]

        # 更新报表中对应行的“2023年”数据
        temp_data.at[index, '2023年'] = matched_rows['2023年'].values[
                                            0] / dw / dw2 if not matched_rows.empty else None
        temp_data.at[index, '2022年'] = matched_rows['2022年'].values[
                                            0] / dw / dw2 if not matched_rows.empty else None
        temp_data.at[index, '2021年'] = matched_rows['2021年'].values[
                                            0] / dw / dw2 if not matched_rows.empty else None

    # 遍历公式
    for index, row in temp_data.iterrows():
        try:
            name_date = row['指标']
            calculation_data = row['计算公式']
            if pd.isna(name_date):  # 检查是否为空
                continue
            if pd.isna(calculation_data):  # 检查是否为空
                continue

            split_data = calculation_data.split('=', 1)[1]

            # 计算右边的表达式
            result1 = evaluate_expression(split_data, source_df, '2023年', '2022年', temp_data)
            result2 = evaluate_expression(split_data, source_df, '2022年', '2021年', temp_data)
            result3 = evaluate_expression(split_data, source_df, '2021年', '2020年', temp_data)

            dw2 = 1
            if dw == 1:
                if '亿元' in name_date:
                    dw2 = 100000000
            if '%' in name_date:
                dw = 1
            # 更新报表中对应行的“2023年”数据
            temp_data.at[index, '2023年'] = result1 / dw / dw2
            temp_data.at[index, '2022年'] = result2 / dw / dw2
            temp_data.at[index, '2021年'] = result3 / dw / dw2

        except Exception as e:
            print(e)


def evaluate_expression(expression, data, time1, time2, mb_data):
    # 替换数据中的变量名为实际值
    for index, row in data.iterrows():
        if pd.isna(row['指标']):  # 检查是否为空
            continue
        # 获取指标名和对应的值
        variable = row['指标']
        value = row[time1]
        # 如果值为空，则赋值0
        if pd.isna(value):
            value = 0
        if variable == '净利润':
            expression = re.sub(rf'\b{re.escape("本期净利润")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("上期净利润")}\b', str(row[time2]), expression)
        if variable == '营业利润':
            expression = re.sub(rf'\b{re.escape("主营业务利润")}\b', str(row[time1]), expression)
        if variable == '营业成本':
            expression = re.sub(rf'\b{re.escape("主营业务成本")}\b', str(row[time1]), expression)
        if variable == '税金及附加':
            expression = re.sub(rf'\b{re.escape("营业税金及附加")}\b', str(row[time1]), expression)
        if variable == '资产总计':
            expression = re.sub(rf'\b{re.escape("期末总资产")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初总资产")}\b', str(row[time2]), expression)
        if variable == '营业收入':
            expression = re.sub(rf'\b{re.escape("本期主营业务收入")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("上期主营业务收入")}\b', str(row[time2]), expression)
            expression = re.sub(rf'\b{re.escape("主营业务收入")}\b', str(row[time1]), expression)
        if variable == '资产总计':
            expression = re.sub(rf'\b{re.escape("期末总资产")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初总资产")}\b', str(row[time2]), expression)
            expression = re.sub(rf'\b{re.escape("总资产")}\b', str(row[time2]), expression)
            matched_rows = mb_data[mb_data['指标'].str.contains('总资产周转率', na=False)]
            expression = re.sub(re.escape("总资产周转率(次)"), str(matched_rows["2023年"].values), expression)
        if variable == '应收账款':
            expression = re.sub(rf'\b{re.escape("期末应收账款")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初应收账款")}\b', str(row[time2]), expression)
            matched_rows = mb_data[mb_data['指标'].str.contains('应收账款周转率', na=False)]
            expression = re.sub(re.escape("应收账款周转率(次)"), str(matched_rows["2023年"].values), expression)
        if variable == '存货':
            expression = re.sub(rf'\b{re.escape("期末存货")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初存货")}\b', str(row[time2]), expression)
            matched_rows = mb_data[mb_data['指标'].str.contains('存货周转率', na=False)]
            expression = re.sub(re.escape("存货周转率"), str(matched_rows["2023年"].values), expression)
        if variable == '固定资产':
            expression = re.sub(rf'\b{re.escape("期末固定资产净值")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初固定资产净值")}\b', str(row[time2]), expression)
        if variable == '流动资产合计':
            expression = re.sub(rf'\b{re.escape("期末流动资产")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初流动资产")}\b', str(row[time2]), expression)
            matched_rows = mb_data[mb_data['指标'].str.contains('流动资产周转率', na=False)]
            expression = re.sub(rf'\b{re.escape("流动资产周转率")}\b', str(matched_rows["2023年"].values), expression)
        if variable == '股东权益合计':
            expression = re.sub(rf'\b{re.escape("期末股东权益合计")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初股东权益合计")}\b', str(row[time2]), expression)

        if variable == '归属于母公司所有者权益合计':
            expression = re.sub(rf'\b{re.escape("归属于母公司股东权益合计")}\b', str(row[time1]), expression)

        # ????
        if variable == '所有者权益合计':
            expression = re.sub(rf'\b{re.escape("股东权益合计")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期末股东权益合计")}\b', str(row[time1]), expression)
            expression = re.sub(rf'\b{re.escape("期初股东权益合计")}\b', str(row[time2]), expression)

        if variable == '负债和股东权益合计':
            expression = re.sub(rf'\b{re.escape("负债和股东权益总计")}\b', str(row[time1]), expression)

        # 替换变量名为实际值
        expression = re.sub(rf'\b{re.escape(variable)}\b', str(value), expression)
        expression = expression.replace('−', '-')
        expression = expression.replace('[', '(')
        expression = expression.replace(']', ')')
        expression = expression.replace('×', '*').replace('%', '/100')
    # # 计算表达式的值
    try:

        result = eval(expression)
        return result
    except Exception as e:
        return None


def d():
    path1 = './data/白云山.xlsx'
    path2 = './data/林园六大财务指标分析-空白模板.xlsx'
    path3 = './data/更新.xlsx'
    path4 = './data/更新.xlsx'
    main(path1, path2, path4)


if __name__ == '__main__':
    d()
